<script type="text/javascript">
    $(document).ready(function() {
        $('#petition').dataTable();

        $("#from").datepicker({
            dateFormat: "yy-mm-dd",
            //showButtonPanel: true,
            defaultDate: "+1w",
            changeMonth: true,
            changeYear: true,
            numberOfMonths: 1,
            onClose: function(selectedDate) {
                $("#to").datepicker("option", "minDate", selectedDate);
            }
        });
        $("#to").datepicker({
            dateFormat: "yy-mm-dd",
            //showButtonPanel: true,
            defaultDate: "+1w",
            changeMonth: true,
            changeYear: true,
            numberOfMonths: 1,
            onClose: function(selectedDate) {
                $("#from").datepicker("option", "maxDate", selectedDate);
            }
        });
    });
</script>

<?php
//require_once("includes/connect.php");
//require_once("includes/function.php");
$db = new myDBS;

$from = $_REQUEST["from"];
$to = $_REQUEST["to"];

if ($from && $to) {
    $where = " AND date_create BETWEEN '$from' AND '$to'";
}

$sql = "SELECT DISTINCT pet_id, date_create, expenses1_name, expenses2_name, net "
        . " FROM tbl_pet, expenses_lvl1, expenses_lvl2 "
        . " WHERE expenses_lvl1.expenses1_id = ex1_id "
        . " AND expenses_lvl2.expenses2_id = ex2_id "
        . " AND approved_status = 'อนุมัติ' "
        . " $where "
        . " UNION "
        . " SELECT DISTINCT p_id AS pet_id, date_create, expenses1_name, expenses2_name, inpNet AS net "
        . " FROM tbl_petition "
        . " LEFT JOIN expenses_lvl1 ON ex1_id = expenses_lvl1.expenses1_id "
        . " LEFT JOIN expenses_lvl2 ON ex2_id = expenses_lvl2.expenses2_id "
        . " WHERE approved_status = 'อนุมัติ' "
        . " $where "
        . " UNION "
        . " SELECT pet_id, date_create, expenses1_name, expenses2_name, sum(salary) AS net "
        . " FROM tbl_salary, expenses_lvl1, expenses_lvl2 "
        . " WHERE "
        . " expenses_lvl1.expenses1_id = ex1_id "
        . " AND expenses_lvl2.expenses2_id = ex2_id "
        . " AND approved_status = 'อนุมัติ' "
        . " $where "
        . " GROUP BY pet_id, expenses1_name, expenses2_name "
        . " UNION "
        . " SELECT pet_id, date_create, expenses1_name, expenses2_name, sum(welfare) AS net "
        . " FROM tbl_welfare, expenses_lvl1, expenses_lvl2 "
        . " WHERE "
        . " expenses_lvl1.expenses1_id = ex1_id "
        . " AND expenses_lvl2.expenses2_id = ex2_id "
        . " AND approved_status = 'อนุมัติ' "
        . " $where "
        . " GROUP BY pet_id, expenses1_name, expenses2_name ";

//echo $sql;

$result = $db->runSQL($sql);
?>

<form action="" method="post" target="_blank">

    <div class="form-inline">
        <label for="from">จาก</label>
        <input type="text" id="from" name="from" required value ="<?php echo $from; ?>" />
        <label for="to">ถึง</label>
        <input type="text" id="to" name="to" required value ="<?php echo $to; ?>"/>
        <button type="submit" id="submit" class="btn btn-primary"><i class="icon-ok icon-white"></i> ดูรายงาน</button>
    </div>


</form>

<table id="petition" class="table table-bordered table-hover" width="100%" >
    <caption><br></caption>
    <thead>
        <tr>
            <th>#</th>
            <th>เลขที่ฎีกา</th>
            <th>วันที่</th>
            <th>หมวด</th>
            <th>ประเภท</th>
            <th>จำนวนเงิน</th>
        </tr>
    </thead>
    <tbody>
        <?php
        $i = 1;
        while ($rows = $db->fetch_object($result)) {
            if ($rows->pet_id == "")
                continue;
            ?>
            <tr>
                <td>&nbsp;<?php echo $i; ?></td>
                <td>&nbsp;<?php echo $rows->pet_id; ?></td>
                <td>&nbsp;<?php echo $rows->date_create; ?></td>
                <td>&nbsp;<?php echo $rows->expenses1_name; ?></td>
                <td>&nbsp;<?php echo $rows->expenses2_name; ?></td>
                <td><p class="text-right">&nbsp;<?php echo number_format($rows->net, 2); ?></p></td>
            </tr>
            <?php
            $i++;
        }
        ?>
    </tbody>
</table>